package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.CashRemittanceByAgentNDetailDataList;
import com.mbc.rmt.data.CashRemittanceToAgentNDetailDataList;
import com.mbc.rmt.entities.CashRemittanceByAgentArchiveCri;
import com.mbc.rmt.entities.CashRemittanceByAgentDetails;
import com.mbc.rmt.entities.CashRemittanceToAgentDetail;
import com.mbc.rmt.entities.CashRemittanceToAgentNDetailCri;

public class CashRemittanceByAgentDetailDAOImpls extends
		DAO<CashRemittanceByAgentDetails> implements
		CashRemittanceByAgentDetailDAO {

	public CashRemittanceByAgentDetailDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "CashRemittanceByAgentDetails";
	}

	@Override
	public void create(CashRemittanceByAgentDetails entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( ReceiptDetailID, ReceiptID, AddCodeID, Sender, Receiver, PaymentAmount, ReceivedAmount, ExtraFees, NRC, Address, ToAgentID, ToBranchID, Rate) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getReceiptDetailId());
		pstmt.setLong(i++, entity.getReceiptId());
		if(entity.getAddcodeId()!=null)
			pstmt.setLong(i++, entity.getAddcodeId());
		else
			pstmt.setString(i++, null);
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setDouble(i++, entity.getPaymentAmt());
		pstmt.setDouble(i++, entity.getReceivedAmt());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		if(entity.getToAgentId()!=null)
			pstmt.setLong(i++, entity.getToAgentId());
		else
			pstmt.setString(i++, null);
		if(entity.getToBranchId()!=null)
			pstmt.setLong(i++, entity.getToBranchId());
		else
			pstmt.setString(i++, null);
		pstmt.setDouble(i++, entity.getRate());
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public void update(CashRemittanceByAgentDetails entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set ReceiptID = ?, AddCodeID = ?, Sender = ?, Receiver = ?, PaymentAmount = ?, ReceivedAmount = ?, ExtraFees = ?, NRC = ?, Address = ?, ToAgentID = ?, ToBranchID = ? , Rate = ? where ReceiptDetailID = "
				+ entity.getReceiptDetailId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getReceiptId());
		if(entity.getAddcodeId()!=null)
			pstmt.setLong(i++, entity.getAddcodeId());
		else
			pstmt.setString(i++, null);
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setDouble(i++, entity.getPaymentAmt());
		pstmt.setDouble(i++, entity.getReceivedAmt());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		if(entity.getToAgentId()!=null)
			pstmt.setLong(i++, entity.getToAgentId());
		else
			pstmt.setString(i++, null);
		if(entity.getToBranchId()!=null)
			pstmt.setLong(i++, entity.getToBranchId());
		else
			pstmt.setString(i++, null);
		pstmt.setDouble(i++, entity.getRate());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public CashRemittanceByAgentDetails find(long key) throws SQLException {
		CashRemittanceByAgentDetails l_info = null;
		query = getSelectQuery() + " where ReceiptDetailID = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_info = readRecord(rs);
			} while (rs.next());
		}
		rs.close();
		pstmt.close();

		return l_info;
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where ReceiptDetailID = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void deleteByReceiptId(long ReceiptId) throws SQLException {
		query = getDeleteQuery() + " where ReceiptID = " + ReceiptId;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	protected CashRemittanceByAgentDetails readRecord(ResultSet rs)
			throws SQLException {
		CashRemittanceByAgentDetails l_entity = new CashRemittanceByAgentDetails();
		l_entity.setReceiptDetailId(rs.getLong("ReceiptDetailID"));
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setAddcodeId(rs.getLong("AddCodeID"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setPaymentAmt(rs.getDouble("PaymentAmount"));
		l_entity.setReceivedAmt(rs.getDouble("ReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setToAgentId(rs.getLong("ToAgentID"));
		l_entity.setToBranchId(rs.getLong("ToBranchID"));
		l_entity.setRate(rs.getDouble("Rate"));
		return l_entity;
	}

	public ArrayList<CashRemittanceByAgentDetails> findCRByAgentDetails(
			long ReceiptId) throws SQLException {
		ArrayList<CashRemittanceByAgentDetails> l_list = new ArrayList<CashRemittanceByAgentDetails>();

		query = " select crad.ReceiptDetailID,crad.ReceiptID, "
				+ " crad.AddCodeID,ac.Code,crad.Sender,crad.Receiver, "
				+ " crad.PaymentAmount,crad.ReceivedAmount,crad.ExtraFees, "
				+ " crad.NRC,crad.Address,crad.ToAgentID,isnull(a.agent_code,'')ToAgentCode,isnull(a.name,'')ToAgentName, "
				+ " crad.ToBranchID,crad.Rate,cr.CurrencyRateId,cr.Roper,isnull(b.BranchCode,'')ToBranchCode, isnull(b.Name,'')ToBranchName,  "
				+ " (case when cra.PaymentCurrencyID = cra.ReceivedCurrencyID " 
                + " then 's_equal_c'  else 's_Nequal_c'  end ) ratestatus "
				+ " from CashRemittanceByAgentDetails crad "
				+ " left join CashRemittanceByAgent cra on cra.ReceiptID = crad.ReceiptID "
				+ " left join AddCode ac on ac.AddCodeId = crad.AddCodeId "
				+ " left join Agent a on a.agent_id=crad.ToAgentID "
				+ " left join Branch b on b.BranchId=crad.ToBranchID "
				+ " left join Currency c on c.CurrencyId=cra.PaymentCurrencyID " 
                + " left join CurrencyRate cr on cr.SourceCurrencyId=cra.PaymentCurrencyID "
				+ " and cr.ConvertCurrencyId=cra.ReceivedCurrencyID " 
                + " and cra.DefaultBranchID=cr.BranchId "
				+ " where crad.ReceiptID = " + ReceiptId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_list.add(readRecords(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_list;
	}

	protected CashRemittanceByAgentDetails readRecords(ResultSet rs)
			throws SQLException {
		CashRemittanceByAgentDetails l_entity = new CashRemittanceByAgentDetails();
		l_entity.setReceiptDetailId(rs.getLong("ReceiptDetailID"));
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setAddcodeId(rs.getLong("AddCodeID"));
		l_entity.setCode(rs.getString("Code"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setPaymentAmt(rs.getDouble("PaymentAmount"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setRateId(rs.getLong("CurrencyRateId"));
		l_entity.setRateOperator(rs.getString("Roper"));
		l_entity.setReceivedAmt(rs.getDouble("ReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setToAgentId(rs.getLong("ToAgentID"));
		l_entity.setToAgentCode(rs.getString("ToAgentCode"));
		l_entity.setToAgentName(rs.getString("ToAgentName"));
		l_entity.setToBranchId(rs.getLong("ToBranchID"));
		l_entity.setToBranchCode(rs.getString("ToBranchCode"));
		l_entity.setToBranchName(rs.getString("ToBranchName"));
		l_entity.setRateStatus(rs.getString("RateStatus"));
		return l_entity;
	}

	@Override
	public ArrayList<CashRemittanceByAgentDetails> findforConfirmDetail(long key)
			throws SQLException {
		ArrayList<CashRemittanceByAgentDetails> l_detailList = new ArrayList<CashRemittanceByAgentDetails>();
		query = "select ROW_NUMBER()over (order by crad.ReceiptDetailID)No, "
				+ " cra.ReceiptNo,crad.Sender, crad.PaymentAmount, "
				+ " crad.ExtraFees,crad.Receiver,crad.NRC,crad.Address,crad.AddCodeID,"
				+ " ac.Code,crad.ToAgentID,ag.agent_code,crad.ToBranchID, b.BranchCode, "
				+ " crad.ReceivedAmount "
				+ " from CashRemittanceByAgentDetails crad "
				+ " inner join CashRemittanceByAgent cra on crad.ReceiptID = cra.ReceiptID  "
				+ " inner join Currency c on c.CurrencyId = cra.PaymentCurrencyID "
				+ " inner join AddCode ac on ac.AddCodeId = crad.AddCodeID "
				+ " left join Agent ag on ag.agent_id = crad.ToAgentID "
				+ " left join Branch b on b.BranchId = crad.ToBranchID "
				+ "inner join Currency cc on cc.CurrencyId = cra.ReceivedCurrencyID "
				+ " where cra.ReceiptID = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_detailList.add(readRecord1(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_detailList;
	}

	private CashRemittanceByAgentDetails readRecord1(ResultSet rs)
			throws SQLException {
		CashRemittanceByAgentDetails l_entity = new CashRemittanceByAgentDetails();
		l_entity.setReceiptDetailId(rs.getLong("ReceiptDetailID"));
		l_entity.setReceiptId(rs.getLong("ReceiptNo"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setPaymentAmt(rs.getDouble("PaymentAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setAddcodeId(rs.getLong("AddCodeID"));
		l_entity.setCode(rs.getString("Code"));
		l_entity.setToAgentId(rs.getLong("ToAgentID"));
		l_entity.setToAgentCode(rs.getString("agent_code"));
		l_entity.setToBranchId(rs.getLong("ToBranchID"));
		l_entity.setToBranchCode(rs.getString("BranchCode"));
		l_entity.setReceivedAmt(rs.getDouble("ReceivedAmount"));
		return l_entity;
	}

	@Override
	public CashRemittanceByAgentNDetailDataList findforConfirm(
			CashRemittanceByAgentArchiveCri archivecri, String status)
			throws SQLException {
		CashRemittanceByAgentNDetailDataList l_CRByAgent = new CashRemittanceByAgentNDetailDataList();
		ArrayList<CashRemittanceByAgentDetails> l_entityList = new ArrayList<CashRemittanceByAgentDetails>();

		if (status.equalsIgnoreCase("agent")) {
			String l_cri = " where 1 = 1 and TransactionType = 1 and cra.DefaultBranchID = "+ archivecri.getBranchId();
			if (!archivecri.getFromAgent().equals("")
					&& !archivecri.getToAgent().equals("")) {
				l_cri += " and (ag.agent_code between '"
						+ archivecri.getFromAgent() + "' and '"
						+ archivecri.getToAgent() + "')";
			} else if (archivecri.getFromAgent().equals("")
					&& archivecri.getToAgent().equals("")) {

			} else if (archivecri.getFromAgent().equalsIgnoreCase(
					archivecri.getToAgent())) {
				l_cri += " and (ag.agent_code between '"
						+ archivecri.getFromAgent() + "' and '"
						+ archivecri.getToAgent() + "')";
			} else {
				if (!archivecri.getFromAgent().equals("")) {
					l_cri += " and ag.agent_code >= '"
							+ archivecri.getFromAgent() + "'";
				} else {
					l_cri += " and ag.agent_code <= '"
							+ archivecri.getToAgent() + "'";
				}
			}

			if (!archivecri.getFromBranch().equals("")
					&& !archivecri.getToBranch().equals("")) {
				l_cri += " and b.BranchCode between '"
						+ archivecri.getFromBranch() + "' and '"
						+ archivecri.getToBranch() + "'";
			} else if (archivecri.getFromBranch().equals("")
					&& archivecri.getToBranch().equals("")) {

			} else if (archivecri.getFromBranch().equalsIgnoreCase(
					archivecri.getToBranch())) {
				l_cri += " and (ag.agent_code between '"
						+ archivecri.getFromBranch() + "' and '"
						+ archivecri.getToBranch() + "')";
			} else {
				if (!archivecri.getFromBranch().equals("")) {
					l_cri += " and b.BranchCode >= '"
							+ archivecri.getFromBranch() + "'";
				} else {
					l_cri += " and b.BranchCode <= '"
							+ archivecri.getToBranch() + "'";
				}
			}
			
			query = "set rowcount "
					+ archivecri.getSize()
					+ " select ### from (select ROW_NUMBER()over (order by ReceiptDetailID)No, "
					+ // * from ( select
					" crad.ReceiptDetailID,cra.ReceiptNo,cra.ReceiptID,crad.Sender,cra.PaymentCurrencyID, "
					+ " c.CurrencyCode PaymentCurrency,cra.Rate,crad.PaymentAmount,"
					+ " crad.ExtraFees,crad.Receiver,crad.NRC,crad.Address,crad.AddCodeID, "
					+ " ac.Code,crad.ToAgentID,ag.agent_code,crad.ToBranchID, b.BranchCode, "
					+ " cra.ReceivedCurrencyID, cc.CurrencyCode ReceivedCurrency, "
					+ " crad.ReceivedAmount "
					+ " from CashRemittanceByAgentDetails crad "
					+ " left join CashRemittanceByAgent cra on crad.ReceiptID = cra.ReceiptID "
					+ " left join Currency c on c.CurrencyId = cra.PaymentCurrencyID "
					+ " left join AddCode ac on ac.AddCodeId = crad.AddCodeID "
					+ " left join Agent ag on ag.agent_id = crad.ToAgentID "
					+ " left join Branch b on b.BranchId = crad.ToBranchID "
					+ " left join Currency cc on cc.CurrencyId = cra.ReceivedCurrencyID"
					+ l_cri ;
			query += ") ab";
			
			String l_query2 = query.replaceAll("###", "count (*)");
			
			query += " where No > " + archivecri.getIndex();
			
			String l_query = query.replaceAll("###", "*");
			
			
			pstmt = conn.prepareStatement(l_query);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				l_entityList.add(readRecordForConfirm(rs));
			}
			rs.close();
			pstmt.close();
			l_CRByAgent.setEntityList(l_entityList);
			/*query = "select count(ReceiptDetailID) from "
					+ table_name
					+ " cra left join CashRemittanceByAgent cr on cr.ReceiptID = cra.ReceiptID left join Agent ag on ag.agent_id = cra.ToAgentID  left join Branch b on b.BranchId = cra.ToBranchID "
					+ " and cr.DefaultBranchID = "+archivecri.getBranchId();*/
			pstmt = conn.prepareStatement(l_query2);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				l_CRByAgent.setTotalRecord(rs.getLong(1));
			}
		} else {
			String l_cri = " where 1 = 1 ";
			String l_cri1 = " where 1 = 1 ";
			String l_cri2 = " where 1 = 1 ";
			if (!archivecri.getFromAgent().equals("")
					&& !archivecri.getToAgent().equals("")) {
				l_cri2 += " and (ab.agent_code between '"
						+ archivecri.getFromAgent() + "' and '"
						+ archivecri.getToAgent() + "')";
			} else if (archivecri.getFromAgent().equals("")
					&& archivecri.getToAgent().equals("")) {

			} else if (archivecri.getFromAgent().equalsIgnoreCase(
					archivecri.getToAgent())) {
				l_cri2 += " and (ab.agent_code between '"
						+ archivecri.getFromAgent() + "' and '"
						+ archivecri.getToAgent() + "')";
			} else {
				if (!archivecri.getFromAgent().equals("")) {
					l_cri2 += " and ab.agent_code >= '"
							+ archivecri.getFromAgent() + "'";
				} else {
					l_cri2 += " and ab.agent_code <= '"
							+ archivecri.getToAgent() + "'";
				}
			}

			if (!archivecri.getFromBranch().equals("")
					&& !archivecri.getToBranch().equals("")) {
				l_cri2 += " and BranchCode between '"
						+ archivecri.getFromBranch() + "' and '"
						+ archivecri.getToBranch() + "'";
				l_cri1 += " and BranchCode between '"
						+ archivecri.getFromBranch() + "' and '"
						+ archivecri.getToBranch() + "'";
			} else if (archivecri.getFromBranch().equals("")
					&& archivecri.getToBranch().equals("")) {

			} else if (archivecri.getFromBranch().equalsIgnoreCase(
					archivecri.getToBranch())) {
				l_cri2 += " and (ab.agent_code between '"
						+ archivecri.getFromBranch() + "' and '"
						+ archivecri.getToBranch() + "')";
				l_cri1 += " and (ab.agent_code between '"
						+ archivecri.getFromBranch() + "' and '"
						+ archivecri.getToBranch() + "')";
			} else {
				if (!archivecri.getFromBranch().equals("")) {
					l_cri2 += " and BranchCode >= '"
							+ archivecri.getFromBranch() + "'";
					l_cri1 += " and BranchCode >= '"
							+ archivecri.getFromBranch() + "'";
				} else {
					l_cri2 += " and BranchCode <= '"
							+ archivecri.getToBranch() + "'";
					l_cri1 += " and BranchCode <= '"
							+ archivecri.getToBranch() + "'";
				}
			}
			query = "set rowcount "
					+ archivecri.getSize()
					+ " select ### from (select ROW_NUMBER()over (order by ReceiptDetailID)No,* from ( select "
					+ " crad.ReceiptDetailID,cra.ReceiptNo,cra.ReceiptID,crad.Sender,cra.PaymentCurrencyID, "
					+ " c.CurrencyCode PaymentCurrency,cra.Rate,crad.PaymentAmount,"
					+ " crad.ExtraFees,crad.Receiver,crad.NRC,crad.Address,crad.AddCodeID, "
					+ " ac.Code,crad.ToAgentID,ag.agent_code,crad.ToBranchID, b.BranchCode, "
					+ " cra.ReceivedCurrencyID, cc.CurrencyCode ReceivedCurrency, "
					+ " crad.ReceivedAmount "
					+ " from CashRemittanceByAgentDetails crad "
					+ " inner join CashRemittanceByAgent cra on crad.ReceiptID = cra.ReceiptID "
					+ " inner join Currency c on c.CurrencyId = cra.PaymentCurrencyID "
					+ " inner join AddCode ac on ac.AddCodeId = crad.AddCodeID "
					+ " left join Agent ag on ag.agent_id = crad.ToAgentID "
					+ " left join Branch b on b.BranchId = crad.ToBranchID "
					+ " inner join Currency cc on cc.CurrencyId = cra.ReceivedCurrencyID"
					+ l_cri +" and TransactionType = 0 and cra.DefaultBranchID = " + archivecri.getBranchId()
					+ " union "
					+ " select '',a.ReceiptNo,a.ReceivedId,a.Sender,a.PaidCurrencyId,"
					+ " cc.CurrencyCode PaymentCurrency,a.Rate,a.PaidAmount,a.ExtraFees,a.Receiver,"
					+ " a.NRC,a.Address,a.AddCodeId,b.Code,'','',a.IssuedBranchId,"
					+ " d.BranchCode,a.ReceivedCurrenyId,ccc.CurrencyCode ReceivedCurrency,"
					+ " a.ReceivedAmount from RemittanceCashReceived a "
					+ " inner join Addcode b on a.AddCodeId = b.AddCodeId "
					+ " inner join Branch d on a.IssuedBranchId = d.BranchId "
					+ " inner join Currency cc on cc.CurrencyId = a.PaidCurrencyId "
					+ " inner join Currency ccc on ccc.CurrencyId = a.ReceivedCurrenyId "+l_cri1+ "and a.BranchId = "+ archivecri.getBranchId();
			query += ") ab " + l_cri2 + " ) ba";
			
			String l_query2 = "";
			l_query2 += query.replaceAll("###", "count (*)");
			
			query += " where No > " + archivecri.getIndex();
		
			String l_query = query.replaceAll("###", "*");
			
			pstmt = conn.prepareStatement(l_query);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				l_entityList.add(readRecordForConfirm(rs));
			}
			rs.close();
			pstmt.close();
			l_CRByAgent.setEntityList(l_entityList);
			
			/*query = " select count(*) from (select ReceiptDetailID "
					+ " from CashRemittanceByAgentDetails cra "
					+ " left join CashRemittanceByAgent cr on cr.ReceiptID = cra.ReceiptID"
					+ " left join Agent ag on ag.agent_id = cra.ToAgentID "
					+ " left join Branch b on b.BranchId = cra.ToBranchID " + l_cri +" and TransactionType = 0 "
					+ " union " + " select ReceivedId "
					+ " from RemittanceCashReceived rcr "
					+ " left join Branch b on b.BranchId = rcr.IssuedBranchId " + l_cri1
					+ " ) a " ;*/

			pstmt = conn.prepareStatement(l_query2);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				l_CRByAgent.setTotalRecord(rs.getLong(1));
			}
		}

		rs.close();
		pstmt.close();
		return l_CRByAgent;
	}

	private CashRemittanceByAgentDetails readRecordForConfirm(ResultSet rs)
			throws SQLException {
		CashRemittanceByAgentDetails l_entity = new CashRemittanceByAgentDetails();
		l_entity.setReceiptDetailId(rs.getLong("ReceiptDetailID"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setPcurrencyId(rs.getLong("PaymentCurrencyID"));
		l_entity.setP_currencyCode(rs.getString("PaymentCurrency"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setPaymentAmt(rs.getDouble("PaymentAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setAddcodeId(rs.getLong("AddCodeID"));
		l_entity.setCode(rs.getString("Code"));
		l_entity.setToAgentId(rs.getLong("ToAgentID"));
		l_entity.setToAgentCode(rs.getString("agent_code"));
		l_entity.setToBranchId(rs.getLong("ToBranchID"));
		l_entity.setToBranchCode(rs.getString("BranchCode"));
		l_entity.setRcurrencyId(rs.getLong("ReceivedCurrencyID"));
		l_entity.setR_currencyCode(rs.getString("ReceivedCurrency"));
		l_entity.setReceivedAmt(rs.getDouble("ReceivedAmount"));
		return l_entity;
	}

	@Override
	public CashRemittanceToAgentNDetailDataList findforCRToAgent(
			CashRemittanceToAgentNDetailCri archivecri) throws SQLException {
		CashRemittanceToAgentNDetailDataList l_CRByAgent = new CashRemittanceToAgentNDetailDataList();
		ArrayList<CashRemittanceToAgentDetail> l_entityList = new ArrayList<CashRemittanceToAgentDetail>();

			String l_cri = " where 1 = 1 and cr.Status = 2 and ag.agent_code = '" + archivecri.getToAgentID() 
//							+ "' and DefaultBranchID = '" + archivecri.getBranchId() + "'"
							+ "' and PaymentCurrencyID = '" + archivecri.getPaymentCurrencyCode() + "'"
							+ " and ReceivedCurrencyID = '" + archivecri.getReceivedCurrencyCode() + "'"
							+ " and crd.Status is null ";
			
			if(archivecri.isAll())
				l_cri += "";
			else
				l_cri += " and DefaultBranchID = '" + archivecri.getBranchId() + "'";
			
//			if(archivecri.getBranchId() != archivecri.get)

			query = "set rowcount "
					+ archivecri.getSize()
					+ " select ### from (select ROW_NUMBER()over (order by cr.ReceiptID)No,crd.ReceiptDetailID,cr.FromAgentID, "
					+ " (case when cr.FromAgentID = 0 then '' else fa.agent_code end) agent_code,  "
					+ " cr.DefaultBranchID, "
					+ " (case when cr.FromAgentID = 0 then br.BranchCode else '' end) BranchCode, "
					+ " cr.ReceiptID,cr.ReceiptNo,crd.AddCodeID,ac.Code,crd.Sender,crd.Receiver,crd.PaymentAmount, "
					+ " cr.Rate,crd.ReceivedAmount,crd.ExtraFees,crd.NRC,crd.Address "
					+ " from CashRemittanceByAgent_Archive cr "
					+ " left join CashRemittanceByAgentDetails_Archive crd on crd.ReceiptID = cr.ReceiptID "
					+ " left join Agent ag on ag.agent_id = crd.ToAgentID " 
					+ " left join Agent fa on fa.agent_id = cr.FromAgentID"
					+ " left join Branch br on br.BranchId = cr.DefaultBranchID "
					+ " left join AddCode ac on ac.AddCodeId = crd.AddCodeID "
					+ l_cri ;
			query += ") ab"; 
			
			String l_query = query + " where No > " + archivecri.getIndex();// +" and agent_code = '"+archivecri.getToAgentID()+"'";
			
			pstmt = conn.prepareStatement(l_query.replace("###", "*"));
			rs = pstmt.executeQuery();
			while (rs.next()) {
				l_entityList.add(readRecordForCRToAgent(rs));
			}
			rs.close();
			pstmt.close();
			l_CRByAgent.setEntityList(l_entityList);
			/*query = "select count(cr.ReceiptID) from "
					+ " CashRemittanceByAgent_Archive cr left join CashRemittanceByAgentDetails_Archive crd on crd.ReceiptID = cr.ReceiptID left join Agent ag on ag.agent_id = cr.FromAgentID  left join Branch b on b.BranchId = cr.DefaultBranchID "
					+ l_cri;*/
			String l_query2 = query.replace("###", "count(*)");
			pstmt = conn.prepareStatement(l_query2);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				l_CRByAgent.setTotalRecord(rs.getLong(1));
			}
			rs.close();
			pstmt.close();
			return l_CRByAgent;
	}

	private CashRemittanceToAgentDetail readRecordForCRToAgent(ResultSet rs)throws SQLException {
		CashRemittanceToAgentDetail l_entity = new CashRemittanceToAgentDetail();
		l_entity.setReceiptDetailID(rs.getLong("ReceiptDetailID"));
		l_entity.setFromAgentID(rs.getLong("FromAgentID"));
		l_entity.setFromAgentCode(rs.getString("agent_code"));
		l_entity.setFromBranchID(rs.getLong("DefaultBranchID"));
		l_entity.setFromBranchCode(rs.getString("BranchCode"));
		l_entity.setReceiptID(rs.getLong("ReceiptID"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setAddCodeID(rs.getLong("AddCodeID"));
		l_entity.setAddCodeC(rs.getString("Code"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setPaymentAmount(rs.getLong("PaymentAmount"));
		l_entity.setRate(rs.getLong("Rate"));
		l_entity.setReceivedAmount(rs.getLong("ReceivedAmount"));
		l_entity.setExtraFee(rs.getLong("ExtraFees"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		return l_entity;
	}
}
